Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Look at the chief complaint and try to understand how that component works and what can be affecting it. Suppose that you are having a client/server response time problem. The following sidebar analyzes this problem.

Response Time Example

The user response time is measured from when the data is input and submitted until the results are displayed on the user’s display device. To analyze a response time problem, start by considering the components. The problem can be broken into three major areas:

  Client. The problem could be with the client application code.
  Server. The problem could be with the database engine.
  Network. Some problem in the network could be delaying processing.

You can use analysis tools to help determine the area to which you can isolate the response time problem. By putting debug statements into the application code, you can determine whether the delays are part of the client component. By using the EXPLAIN PLAN command, you may see that you are not taking advantage of an index and are causing excessive table scans.

I have heard of several examples in client/server systems in which the end user complains about the performance of the server: it is causing 10-second or greater response times. After analysis, it was determined that the database was giving subsecond response times but was spending over 9 seconds in the GUI (Graphical User Interface).

Be open minded and look at every component of the system as a potential for bottlenecks. You may be surprised to find that the problem is something simple and in an area you would not normally think would cause problems.

Here are some questions to ask yourself when trying to determine the cause of the performance problem:

  What are the effects of the problem: response times? throughput? what?
  Does every user see the same problem or does the problem affect just a few users? If so, what are those few users doing differently?
  What does the system monitor tell you? Are you 100-percent CPU bound? What about I/Os: are you exceeding I/O limits?
  Is only a certain type of transaction a problem? What about other transaction types? Are they okay?
  Is it a client problem or a server problem?

By asking yourself these questions and making up your implementation-specific questions, you will get more insight into how the application operates and where the potential bottlenecks are.

Determine the Solution and Set Goals

Regardless of the type of problem you have, you must determine a course of action. In many cases, determining the problem has already pointed you to a solution. In other cases, more analysis is necessary. Once the problem has been determined, you must set some goals about what you want the solution to accomplish. When you set specific goals, not only can you measure success, but specific goals may sometimes point you to specific solutions.

The solution may consist of changing a parameter in the OS or in Oracle; the solution may consist of a rewrite of a section of application code. With packaged applications, you may have to tune the OS and database to overcome a problem in the application.

The determination of a solution should include some expectation of what results you expect to see. Changing a parameter without at least knowing what it is supposed to do may provide the desired results or may instead mask the true problem.

Goal Setting

Once you determine the problem (or at least have a pretty good idea about the area causing the problem), you should set some goals. Your ultimate goal is always optimal performance but here I want you to think more specifically.

Your goals should be specific and achievable. Here are some examples of realistic and achievable goals:

  Increase the cache-hit ratio in the buffer cache.
  Reduce the number of I/Os.
  Increase the number of connections supported.

Goals such as these may or may not be achievable in your system but at least they give you an area on which to focus your tuning efforts. Unachievable goals always end in disappointment. Although we all have the goal to have the best-performing system possible, smaller, more specific goals can help you achieve the larger goal more effectively.

Here are some questions to ask yourself when you are determining the solution and setting result goals:

  What will this solution do? What do you expect the result of your changes to be?
  How will your changes affect performance? For example, does a better cache-hit rate means less I/Os?
  Will this solution directly or indirectly affect the problem?
  How will your idea of the problem change if this solution doesn’t fix the problem?

These are just a few examples of the kind of things to think about when determining the solution and setting goals.

Test the Solution

Once you put the solution in place, you must test the solution to see whether you have achieved the desired results. Although you may be able to test the results in a test configuration by setting up an emulated load, you may have no choice but to put the system into the production environment for analysis.

During the testing stage, it is also very important to take notes. These notes can be extremely valuable in the future when similar problems arise and you are looking for solutions.


CAUTION:  I don’t recommend putting test configurations into production unless you completely understand the changes that have been made and have assessed the risks.

In most cases, you will have the test programs and user-emulation facilities you used in the development phase. You can use these to test the new configuration against already-known performance results. Refer to Chapter 5, “Benchmarking,” for more information on this topic.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.